﻿using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Models.Entitys;
using RndMaterialDisposalService.Models.RequestModel;
using RndMaterialDisposalService.Repository;
using RndMaterialDisposalService.Services.Interface;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using NPOI.SS.Formula.Functions;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace RndMaterialDisposalService.Services
{
    /// <summary>
    /// 
    /// </summary>
    [NAutowired(Lifecycle.Scoped)]
    public class ERPAssetInfoService : IERPAssetInfoService
    {
        private AssetsManagementContext dbContext;
        private RNDBVDataContext RNDBVContext;
        private EDPMiddleContext MiddleContext;
        private K2Context k2Context;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_dbContext"></param>
        /// <param name="_k2Context"></param>
        /// <param name="_RNDBVContext"></param>
        /// <param name="_middleContext"></param>
        public ERPAssetInfoService(AssetsManagementContext _dbContext, K2Context _k2Context, RNDBVDataContext _RNDBVContext, EDPMiddleContext _middleContext)
        {
            dbContext = _dbContext;
            k2Context = _k2Context;
            RNDBVContext = _RNDBVContext;
            MiddleContext = _middleContext;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="assetInfoReq"></param>
        /// <returns></returns>
        public async Task<(List<ERPAssetInfoModel> Data, int TotalCount)> GetERPAssetInfo(ERPAssetInfoReq assetInfoReq)
        {
            var query = dbContext.ERPAssetInfo.Where(p => 1 == 1);
            if (!string.IsNullOrEmpty(assetInfoReq.UseID))
            {
                query = query.Where(p => p.OwnerEmployeeNumber == assetInfoReq.UseID);
            }
            if (!string.IsNullOrEmpty(assetInfoReq.UseName))
            {
                query = query.Where(p => p.AssetOwnerName == assetInfoReq.UseName);
            }
            if (!string.IsNullOrEmpty(assetInfoReq.AssetName))
            {
                query = query.Where(p => p.AssetDesription!.Contains(assetInfoReq.AssetName));
            }
            if (!string.IsNullOrEmpty(assetInfoReq.AssetCode))
            {
                query = query.Where(p => p.AssetNumber!.StartsWith(assetInfoReq.AssetCode));
            }
            if (!string.IsNullOrEmpty(assetInfoReq.DeptCode))
            {
                query = query.Where(p => p.DeptNumber!.StartsWith(assetInfoReq.DeptCode));
            }
            if (assetInfoReq.GetStartDate != null)
            {
                query = query.Where(p => p.GetDate >= assetInfoReq.GetStartDate);
            }
            if (assetInfoReq.GetEndDate != null)
            {
                query = query.Where(p => p.GetDate <= assetInfoReq.GetEndDate);
            }
            if (!string.IsNullOrWhiteSpace(assetInfoReq.BookType))
            {
                query = query.Where(p => p.BookType == assetInfoReq.BookType);
            }

            var totalCount = query.Count();
            var data = await query.Skip((assetInfoReq.PageNum - 1) * assetInfoReq.PageSize).Take(assetInfoReq.PageSize).ToListAsync();
            return (data, totalCount);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="bookType"></param>
        /// <param name="pageNum"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<ERPAssetInfoModel> Data, int TotalCount)> GetAssetInfoByKeyword(string? keyword, string? bookType, int pageNum = 1, int pageSize = 10)
        {
            var query = dbContext.ERPAssetInfo.Where(p => 1 == 1);
            if (!string.IsNullOrEmpty(keyword))
            {
                query = query.Where(p => (string.IsNullOrEmpty(p.AssetNumber)==false && p.AssetNumber!.Contains(keyword))
                || (string.IsNullOrEmpty(p.AssetDesription) == false && p.AssetDesription!.Contains(keyword))
                || p.OwnerEmployeeNumber == keyword
                || p.AssetOwnerName == keyword);
            }
            if (!string.IsNullOrEmpty(bookType))
            {
                query = query.Where(p => p.BookType == bookType);
            }
            
            var totalCount = query.Count();
            var data = await query.Skip((pageNum - 1) * pageSize).Take(pageSize).ToListAsync();
            return (data, totalCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> SyncERPAssetData()
        {
            return await dbContext.Database.ExecuteSqlRawAsync("EXEC dbo.ERPAssetInfoSynchro_SP");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<object>> ExecSqlQuery(string sql)
        {
            using (var command = dbContext.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                dbContext.Database.OpenConnection();
                using (var result = await command.ExecuteReaderAsync())
                {
                    var entities = new List<object>();
                    while (result.Read())
                    {
                        var dict = new Dictionary<string, object>();
                        for (int i = 0; i < result.FieldCount; i++)
                        {
                            var columnName = result.GetName(i);
                            var colDataType = result.GetFieldType(i);
                            var val = result.GetFieldValue<object>(i);
                            if (val == DBNull.Value)
                            {
                                val = "";
                            }
                            dict.Add(columnName, val);
                        }
                        entities.Add(dict);
                    }
                    return entities;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<int> ExecSqlUpdate(string sql)
        {
            return await dbContext.Database.ExecuteSqlRawAsync(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<object>> K2ExecSqlQuery(string sql)
        {
            using (var command = k2Context.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                k2Context.Database.OpenConnection();
                using (var result = await command.ExecuteReaderAsync())
                {
                    var entities = new List<object>();
                    while (result.Read())
                    {
                        var dict = new Dictionary<string, object>();
                        for (int i = 0; i < result.FieldCount; i++)
                        {
                            var columnName = result.GetName(i);
                            var colDataType = result.GetFieldType(i);
                            var val = result.GetFieldValue<object>(i);
                            if (val == DBNull.Value)
                            {
                                val = "";
                            }
                            dict.Add(columnName, val);
                        }
                        entities.Add(dict);
                    }
                    return entities;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<int> K2ExecSqlUpdate(string sql)
        {
            return await k2Context.Database.ExecuteSqlRawAsync(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<ProjectInfoDto> Data, int TotalCount)> GetProjectInfoByKeyword(string? keyword, int pageIndex = 1, int pageSize = 10)
        {
            var query = RNDBVContext.ProjectInfo.Where(p => 1 == 1);
            if (!string.IsNullOrEmpty(keyword))
            {
                query = query.Where(p => (!string.IsNullOrEmpty(p.ProjectName) && p.ProjectName.Contains(keyword))
                || (!string.IsNullOrEmpty(p.ProjectCode) && p.ProjectCode.Contains(keyword)));
            }

            var totalCount = await query.CountAsync();
            var data = await query.OrderBy(p => p.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
            return (data, totalCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<ProductModelDto> Data, int TotalCount)> GetProductModelByKeyword(string? keyword, int pageIndex = 1, int pageSize = 10)
        {
            var query = RNDBVContext.ProductModel.Where(p => 1 == 1);
            if (!string.IsNullOrEmpty(keyword))
            {
                query = query.Where(p => (!string.IsNullOrEmpty(p.IntMod) && p.IntMod.Contains(keyword))
                || (!string.IsNullOrEmpty(p.ExtMod) && p.ExtMod.Contains(keyword))
                || (!string.IsNullOrEmpty(p.PRJCODE) && p.PRJCODE.Contains(keyword))
                || (!string.IsNullOrEmpty(p.ItemID) && p.ItemID.Contains(keyword))
                || (!string.IsNullOrEmpty(p.Describe) && p.Describe.Contains(keyword)));
            }

            var totalCount = await query.CountAsync();
            var data = await query.OrderBy(p => p.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
            return (data, totalCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="projectCode"></param>
        /// <returns></returns>
        public async Task<List<ProductModelDto>> ProductModelByPrjcode(string projectCode) {
            //return await RNDBVContext.ProductModel.Where(p => string.Equals(p.PRJCODE,projectCode,StringComparison.OrdinalIgnoreCase)).ToListAsync();
            return await RNDBVContext.ProductModel.Where(p => p.PRJCODE==projectCode).ToListAsync();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<ERPDeviceInfoDto> Data, int TotalCount)> GetERPDeviceInfoByKeyword(string? keyword, int pageIndex = 1, int pageSize = 10)
        {
            var query = MiddleContext.ERPDeviceInfo.Where(p => 1 == 1);
            if (!string.IsNullOrEmpty(keyword))
            {
                query = query.Where(p => (!string.IsNullOrEmpty(p.ITEM) && p.ITEM.Contains(keyword))
                || (!string.IsNullOrEmpty(p.DESCRIPTION) && p.DESCRIPTION.Contains(keyword)));
            }

            var totalCount = await query.CountAsync();
            var data = await query.OrderBy(p => p.ImportDateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
            return (data, totalCount);
        }
    }
}
